clear


import excel "$dir/data/data_public/SeriesReport-20250820071921_7f658a.xlsx", sheet("BLS Data Series") cellrange(B4:ACQ5) firstrow

xpose, clear varname
ren v1 cpi

gen year = substr(_varname, 4, 4)
gen month = substr(_varname, 1, 3)

drop if cpi ==.



replace month = "1" if month == "Jan"
replace month = "2" if month == "Feb"
replace month = "3" if month == "Mar"
replace month = "4" if month == "Apr"
replace month = "5" if month == "May"
replace month = "6" if month == "Jun"
replace month = "7" if month == "Jul"
replace month = "8" if month == "Aug"
replace month = "9" if month == "Sep"
replace month = "10" if month == "Oct"
replace month = "11" if month == "Nov"
replace month = "12" if month == "Dec"



destring year, replace
destring month, replace

drop _varname

*want annual cpi so take the average across the year
collapse cpi, by(year)

sum cpi if year == 2007 

gen cpito2007 =cpi/r(mean)

drop cpi
ren cpito2007 cpi

 save "$dir/data/interim/wage_deflation.dta", replace





